﻿using RndMaterialDisposalService.Attrbutes;
using RndMaterialDisposalService.Models;
using RndMaterialDisposalService.Models.Entitys;
using RndMaterialDisposalService.Models.RequestModel;
using RndMaterialDisposalService.Models.ResponseModel;
using RndMaterialDisposalService.Repository;
using RndMaterialDisposalService.Services.Interface;
using AutoMapper;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using NPOI.POIFS.Crypt.Agile;
using System.Collections.Generic;
using System.Data;

namespace RndMaterialDisposalService.Services
{
    /// <summary>
    /// 
    /// </summary>
    [NAutowired(Lifecycle.Scoped)]
    public class CapitalTransferReportFormService: ICapitalTransferReportFormService
    {
        private readonly IMapper mapper;
        private readonly AssetsManagementContext dbContext;
        private readonly UserBaseInforDataContext userBaseInforDataContext;

        /// <summary>
        /// 
        /// </summary>
        /// <param name="_dbContext"></param>
        /// <param name="_edpContext"></param>
        /// <param name="_mapper"></param>
        /// <param name="_userBaseInforDataContext"></param>
        public CapitalTransferReportFormService(AssetsManagementContext _dbContext, EDPPlatformContext _edpContext, IMapper _mapper, UserBaseInforDataContext _userBaseInforDataContext) 
        {
            mapper = _mapper;
            dbContext = _dbContext;
            userBaseInforDataContext = _userBaseInforDataContext;
        }

        /// <summary>
        /// 返回资产转移电子流报表主数据(分页)
        /// </summary>
        /// <param name="inputCondition"></param>
        /// <returns></returns>
        public (List<CapitalTransferReportFormModel> list, int count) GetCapitalTransferReportFormPageList(CapitalTransferReportConditionModel inputCondition) 
        {
            List<SqlParameter> sqlParameters = new List<SqlParameter>();
            string condition = "1=1 ";
            if (!string.IsNullOrWhiteSpace(inputCondition.ApplicationNumber)) 
            {
                condition += $"AND V.ApplicationNumber LIKE '%{inputCondition.ApplicationNumber}%' ";
            }
            if (!string.IsNullOrWhiteSpace(inputCondition.ApplicationTime)) 
            {
                //condition += $"AND V.CreatedDate >= '{inputCondition.ApplicationTime}' ";
                string[] timeArr = inputCondition.ApplicationTime.Split(',');
                if (timeArr.Length > 0)
                {
                    condition += $"AND V.CreatedDate >= '{timeArr[0]} 00:00:00' AND V.CreatedDate <= '{timeArr[1]} 23:59:59' ";
                }
            }
            if (!string.IsNullOrWhiteSpace(inputCondition.OutName)) 
            {
                condition += $"AND V.OutDeptUserID = '{inputCondition.OutName}' ";
            }
            if (!string.IsNullOrWhiteSpace(inputCondition.OutDeptName)) 
            {
                condition += $"AND (V.OutDeptFirstDept LIKE '%{inputCondition.OutDeptName}%' OR V.OutDeptSecondDept LIKE '%{inputCondition.OutDeptName}%' )";
            }
            if (!string.IsNullOrWhiteSpace(inputCondition.InputName)) 
            {
                condition += $"AND V.InputDeptUserID = '{inputCondition.InputName}' ";
            }
            if (!string.IsNullOrWhiteSpace(inputCondition.InputDeptName)) 
            {
                condition += $"AND (V.InputDeptFirstDept LIKE '%{inputCondition.InputDeptName}%' OR V.InputDeptSecondDept LIKE '%{inputCondition.InputDeptName}%' ) ";
            }
            if (!string.IsNullOrWhiteSpace(inputCondition.CurrentNode))
            {
                condition += $"AND V.CurrentStatus = '{inputCondition.CurrentNode}' ";
            }
            if (!string.IsNullOrWhiteSpace(inputCondition.CapitalNo)) 
            {
                condition += $"AND V.AssetNo LIKE '%'+@AssetNo+'%' ";
                SqlParameter sqlPara = new SqlParameter("@AssetNo", SqlDbType.NVarChar);
                sqlPara.Value = inputCondition.CapitalNo;
                sqlParameters.Add(sqlPara);
            }
            if (!string.IsNullOrWhiteSpace(inputCondition.CompanySegment)) {
                condition += $"AND V.CompanyType LIKE '%{inputCondition.CompanySegment}%' ";
            }
            if (!string.IsNullOrWhiteSpace(inputCondition.FirstAdmin)) 
            {
                condition += $"AND V.OutDeptFirstAdmin = '{inputCondition.FirstAdmin}' ";
            }


            string SqlInsertClip = string.Empty;

            List<WorkflowProcInstIdModel> ProcInstIdList = new List<WorkflowProcInstIdModel>();

            //插入当前用户查询业务
            if (!string.IsNullOrWhiteSpace(inputCondition.CurrentUser))
            {
                //获取当前用户资产转移电子流审批流程记录,去重后联查得到ProcInstId
                //string _sql = $"SELECT ProcInstId FROM AssetsManagement.dbo.CapitalTransferBusinessData WHERE ProcessFolio IN (SELECT DISTINCT(ApplicationNumber),(SELECT MIN(ApproveDate) FROM VW_Common.dbo.WorkflowApproveData WHERE ApplicationNumber = a.ApplicationNumber)  AS ApproveDate FROM VW_Common.dbo.WorkflowApproveData WHERE WorkflowName = '资产转移电子流' AND ApproverName = '{inputCondition.CurrentUser}')";
                string _sql = $"SELECT a.ProcInstId,c.ApplicationNumber,c.ApproveDate FROM AssetsManagement.dbo.CapitalTransferBusinessData a LEFT JOIN (SELECT DISTINCT(ApplicationNumber),(SELECT MIN(ApproveDate) FROM VW_Common.dbo.WorkflowApproveData WHERE ApplicationNumber = b.ApplicationNumber AND WorkflowName = '资产转移电子流')  AS ApproveDate FROM VW_Common.dbo.WorkflowApproveData b WHERE WorkflowName = '资产转移电子流' AND ApproverName = '{inputCondition.CurrentUser}') c ON c.ApplicationNumber = a.ProcessFolio WHERE ProcInstId !='' AND ApproveDate IS NOT NULL";
                ProcInstIdList = SqlQuery<WorkflowProcInstIdModel>(_sql,new List<SqlParameter>());

                //审批历史ProcInstId
                var ApprovalHistoryProcInstIdList = ProcInstIdList.Select(a => a.ProcInstId).ToList();

                //当前用户工号
                string employeeNumber = inputCondition.CurrentUser.Substring(0, inputCondition.CurrentUser.IndexOf(" "));
                //利用UserID获取当前用户工号LaunchList
                //var EmployeeInfo = userBaseInforDataContext.UserBaseInfor.FirstOrDefault(x => x.UserId == UserId);
                if (!string.IsNullOrWhiteSpace(employeeNumber))
                {

                    //获取发起流程实例
                    _sql = $"SELECT ProcInstId FROM VW_Common.dbo.WorkflowApplicationData WHERE ProcInstId !='' AND WorkflowName='资产转移电子流' AND ApplicantEmployeeNumber = '{employeeNumber}' ";
                    List<WorkflowProcInstIdModel> LaunchList = SqlQuery<WorkflowProcInstIdModel>(_sql,new List<SqlParameter>());
                    if (LaunchList.Any())
                    {
                        var LaunchProcInstIdList = LaunchList.Select(a => a.ProcInstId).ToList();
                        ApprovalHistoryProcInstIdList.AddRange(LaunchProcInstIdList);
                        ApprovalHistoryProcInstIdList = ApprovalHistoryProcInstIdList.Distinct().ToList();
                    }

                }

                string ProcInstId_STR = "''";
                if (ApprovalHistoryProcInstIdList.Any())
                {
                    List<string> _ProcInstIdList = ApprovalHistoryProcInstIdList.Select(a => a = $"'{a.Trim()}'").ToList();
                    ProcInstId_STR = string.Join(',', _ProcInstIdList);
                }

                SqlInsertClip = $"(SELECT * FROM AssetsManagement.dbo.CapitalTransferBusinessSubtableData WHERE ProcInstId IN({ProcInstId_STR}))";
            }
            else
            {
                SqlInsertClip = "(SELECT * FROM AssetsManagement.dbo.CapitalTransferBusinessSubtableData WHERE ProcInstId !='')";
            }

            int startIndex = ((inputCondition.pageIndex - 1) * inputCondition.pageSize);
            int endIndex = inputCondition.pageSize * inputCondition.pageIndex;

            string sqlCount = @$"SELECT * FROM (SELECT ROW_NUMBER()  OVER(ORDER BY V.CreatedDate DESC) AS RowID,V.* FROM (
                            SELECT repairSub.SysId AS [Key],workflow.ApplicationNumber,workflow.ProcInstId,workflow.ApplicantEmployeeNumber,repairSub.AssetNo,workflow.CurrentHandler AS ApplicantName,repair.OutDeptUserID,repair.OutDeptDeptCode,repair.OutDeptUserName,repair.OutDeptFirstDept,repair.OutDeptSecondDept,repair.InputDeptUserID,repair.InputDeptDeptCode,repair.InputDeptUserName,repair.InputDeptFirstDept,repair.InputDeptSecondDept,workflow.CurrentStatus,workflow.CreatedDate,repair.CompanyType,repairSub.StorageLocation,repair.OutDeptFirstAdmin,repair.InputDeptFirstAdmin,repairSub.ProductSegment  
                            FROM {SqlInsertClip} repairSub 
                            LEFT JOIN AssetsManagement.dbo.CapitalTransferBusinessData repair ON repairSub.ProcInstId = repair.ProcInstId 
                            LEFT JOIN VW_Common.dbo.WorkflowApplicationData workflow ON repairSub.ProcInstId = workflow.ProcInstId) V WHERE {condition}) V 
                            ";

            var data = SqlQuery<CapitalTransferReportFormModel>(sqlCount, sqlParameters);

            data.ForEach((x) => {
                x.ApplicationNumber = x.ApplicationNumber.Trim();
            });

            var count = data.Count;
            var list = data.Skip(startIndex).Take(inputCondition.pageSize).ToList();

            //赋值流程最早时间（个人流程查询）
            if (!string.IsNullOrWhiteSpace(inputCondition.CurrentUser))
            {
                list.ForEach((x) => {
                    var item = ProcInstIdList.FirstOrDefault(a => a.ProcInstId == x.ProcInstId);
                    if (item != null)
                    {
                        x.CreatedDate = item.ApproveDate;
                    }
                });
            }

            ////去掉当前审批人前缀
            //list.ForEach((x) => {
            //    if (!string.IsNullOrWhiteSpace(x.ApplicantName))
            //    {
            //        x.ApplicantName = x.ApplicantName.Substring(x.ApplicantName.IndexOf(" ") + 1).Trim();
            //    }
            //});




            return (list,count);
        }

        /// <summary>
        /// 新增资产转移电子流业务主表数据
        /// </summary>
        /// <param name="formData"></param>
        /// <returns></returns>
        public async Task<int> AddCapitalRepairBusinessData(CapitalTransferElectronFlowReq formData) 
        {
            var repairBusiness = mapper.Map<CapitalTransferBusinessDataModel>(formData);
            var repairBusinessSubtable = mapper.Map<List<CapitalTransferBusinessSubtableDataModel>>(formData.AssetDetail);
            repairBusinessSubtable.ForEach(p => p.ProcInstId = formData.FormHeader.ProcInstId);

            //删除已存在业务数据
            var CapitalTransferBusinessDataItem = await dbContext.CapitalTransferBusinessData.FirstOrDefaultAsync(a => a.ProcInstId == formData.FormHeader.ProcInstId);
            if (CapitalTransferBusinessDataItem != null) 
            {
                dbContext.CapitalTransferBusinessData.Remove(CapitalTransferBusinessDataItem);
            }

            var CapitalTransferBusinessSubtableDataItem = dbContext.CapitalTransferBusinessSubtableData.Where(a => a.ProcInstId == formData.FormHeader.ProcInstId);
            if (CapitalTransferBusinessSubtableDataItem != null) 
            {
                dbContext.CapitalTransferBusinessSubtableData.RemoveRange(CapitalTransferBusinessSubtableDataItem);
            }

            dbContext.CapitalTransferBusinessData.Add(repairBusiness);
            dbContext.CapitalTransferBusinessSubtableData.AddRange(repairBusinessSubtable);
            return await dbContext.SaveChangesAsync();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="sqlParameters"></param>
        /// <returns></returns>
        public List<T> SqlQuery<T>(string sql, List<SqlParameter> sqlParameters) where T:class,new()
        {
            List<T> list = new List<T>();
            var conn = dbContext.Database.GetDbConnection();
            var comm = conn.CreateCommand();
            try
            {
                conn.Open();
                comm.CommandText = sql;
                if (sqlParameters.Count > 0)
                {
                    sqlParameters.ForEach((x) => {
                        comm.Parameters.Add(x);
                    });
                }

                var dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    T t = new T();
                    for (int i = 0; i < dr.FieldCount; i++)
                    {
                        var columnName = dr.GetName(i);
                        var colDataType = dr.GetFieldType(i);
                        var item = dr.GetFieldValue<object>(i);
                        if (item == DBNull.Value)
                        {
                            item = null;
                        }
                        //var fieid = t.GetType().GetField(columnName);
                        //fieid?.SetValue(t, item);
                        var property = t.GetType().GetProperty(columnName);
                        property?.SetValue(t, item);
                    }
                    list.Add(t);
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally 
            {
                if (conn != null && conn.State != System.Data.ConnectionState.Closed)
                {
                    conn.Close();
                }
            }

            return list;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="formDataModify"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<int> AddFormDataModifyLog(FormDataModifyLogDto formDataModify)
        {
            var result = await dbContext.FormDataModifyLog.AddAsync(formDataModify);
            return await dbContext.SaveChangesAsync();
        }
    }
}
